import traceback
from datetime import datetime

import pymysql

startDateStr = '2018-08-01'
endDateStr = '2018-08-30'


def update_settle_channel_payment_by_sql():
    # 准备基本参数
    # 使用 cursor() 方法创建一个游标对象 cursor
    db = pymysql.connect("db1.dev1.yiyao.cc", "mall_root", "20151118", "mall_order", port=13306, charset='utf8')
    cursor = db.cursor()
    selectCount = 0
    updateCount = 0
    exceptionCount = 0
    try:
        settleChannelPaymentList = _getSettleChannelPaymetNeedToHandle(cursor)
        if not settleChannelPaymentList:
            print("查询到的医药订单 settleSnList 信息为空，程序自动退出")
            return
        selectCount = len(settleChannelPaymentList)
        for settleChannelPayment in settleChannelPaymentList:
            sid = settleChannelPayment[0]
            payChannelId = settleChannelPayment[1]
            settleSn = settleChannelPayment[2]
            payAmount = _getPayAmount(cursor, payChannelId, settleSn)
            if not payAmount:
                print(f"结算单号【{settleSn}】,支付渠道 {payChannelId},  查询到的支付价格为空，跳过此项。")
                continue
            deliveryAmount = _getSellerDeliveryAmount(cursor, payChannelId, settleSn)
            if payAmount != deliveryAmount:
                exceptionCount += 1
                print(f"结算单号【{settleSn}】, 结算渠道 {payChannelId},  支付金额 {payAmount}  和出库金额 {deliveryAmount} 不一致；")
                deliveryInfo = _getItemDeliveryInfo(cursor, payChannelId, settleSn)
                print(f"订单出库详情: {deliveryInfo}")
            # print(settleSn, payChannelId, payAmount, deliveryAmount)
            # 执行sql语句
            _updateSettleChannelPayment(cursor, sid, payAmount, deliveryAmount)
            updateCount += 1
        db.commit()
        print(f"执行成功，查询到 {selectCount} 笔结算渠道支付明细， 更新了 {updateCount} 笔结算渠道支付明细, 其中有 {exceptionCount} 笔 支付金额 和出库金额 不一致")
    except Exception as e:
        traceback.print_exc()
        print(e)
        # 发生错误时回滚
        print("发生错误，事务回滚")
        db.rollback()
        print(f"执行失败。")
    finally:
        # 关闭数据库连接
        db.close()


def _updateSettleChannelPayment(cursor, sid, payAmount, deliveryAmount):
    updateSettleChannelPaymentSql = f"""
    UPDATE mall_order.settle_channel_payment
    SET pay_amount = {payAmount},
     seller_deliver_amount = {deliveryAmount}
    WHERE
        sid = {sid}
    """
    # print(updateSettleChannelPaymentSql)
    cursor.execute(updateSettleChannelPaymentSql)


def _getSellerDeliveryAmount(cursor, payChannelId, settleSn):
    itemDeliveryInfoDoList = _getItemDeliveryInfo(cursor, payChannelId, settleSn)
    itemDeliveryInfoList = []
    for itemDeliveryInfoDo in itemDeliveryInfoDoList:
        itemDeliveryInfo = ItemDeliveryInfo(itemDeliveryInfoDo[0], itemDeliveryInfoDo[1], itemDeliveryInfoDo[2],
                                            itemDeliveryInfoDo[3], itemDeliveryInfoDo[4], itemDeliveryInfoDo[5],
                                            itemDeliveryInfoDo[6])
        itemDeliveryInfoList.append(itemDeliveryInfo)

    orderDict = {}
    for itemDeliveryInfo in itemDeliveryInfoList:
        orderSn = itemDeliveryInfo.orderSn
        if orderSn in orderDict.keys():
            orderDeliveryInfo = orderDict[orderSn]
            itemDeliveryAmount = _calculationAmount(itemDeliveryInfo)
            orderDeliveryInfo.orderDeliveryAmount = orderDeliveryInfo.orderDeliveryAmount + itemDeliveryAmount
            orderDict[orderSn] = orderDeliveryInfo
        else:
            shippingFee = itemDeliveryInfo.shippingFee
            orderDeliveryInfo = OrderDeliveryInfo(orderSn)
            itemDeliveryAmount = _calculationAmount(itemDeliveryInfo)
            orderDeliveryInfo.orderDeliveryAmount = shippingFee + itemDeliveryAmount
            orderDict[orderSn] = orderDeliveryInfo

    settleDeliveryAmount = 0.0
    for orderSn, orderDeliveryInfo in orderDict.items():
        settleDeliveryAmount += orderDeliveryInfo.orderDeliveryAmount

    return settleDeliveryAmount


def _getItemDeliveryInfo(cursor, payChannelId, settleSn):
    getItemDeliveryInfoSql = f"""
        SELECT
            om.order_sn orderSn,
            om.seller_id sellerId,
            om.shipping_fee shippingFee,
            oi.item_id itemId,
            IFNULL(oi.price, 0.0) orderPrice,
            IFNULL(oi.final_price, 0.0) deliveryPrice,
            IFNULL(oi.final_count, 0.0) finalCount,
            oi.count
        FROM
            (
                SELECT
                    sid AS order_id,
                    order_sn,
                    seller_id,
                    shipping_fee
                FROM
                    med_order
                WHERE
                    order_sn IN (
                        SELECT
                            os.order_sn
                        FROM
                            mall_order.order_settle os
                        WHERE
                            os.settle_sn = {settleSn}
                        AND os.pay_channel_id = {payChannelId}
                    )
            ) om
        LEFT JOIN order_item oi ON om.order_id = oi.order_id
    	"""
    # print(f"【sql】 获取医药订单lsit的sql 是：{getItemDeliveryInfoSql}")
    cursor.execute(getItemDeliveryInfoSql)
    return cursor.fetchall()


def _calculationAmount(itemDeliveryInfo):
    sellerId = itemDeliveryInfo.sellerId
    if sellerId in (34, 36, 11015, 35451):
        finalPrice = itemDeliveryInfo.deliveryPrice
    else:
        finalPrice = itemDeliveryInfo.orderPrice
    amount = finalPrice * itemDeliveryInfo.finalCount
    if not amount:
        print(f"订单号【{itemDeliveryInfo.orderSn}】itemId{itemDeliveryInfo.itemId} 计算出的出库金额为{amount}")
    return amount


def _getPayAmount(cursor, payChannelId, settleSn):
    getPayAmountSql = f"""
    SELECT
        sum(pay_amount) AS payAmount
    FROM
        mall_order.order_pay_detail
    WHERE
        order_sn IN (
            SELECT
                os.order_sn
            FROM
                mall_order.order_settle os
            WHERE
                os.settle_sn = {settleSn}
            AND os.pay_channel_id = {payChannelId}
	    )
	    AND has_valid = 1
        AND `status` = 1
    """
    # print(f"【sql】 获取医药订单lsit的sql 是：{getPayAmountSql}")
    cursor.execute(getPayAmountSql)
    return cursor.fetchone()[0]


def _getSettleChannelPaymetNeedToHandle(cursor):
    startDate = datetime.strptime(startDateStr + " 05:00:00", '%Y-%m-%d %H:%M:%S')
    endDate = datetime.strptime(endDateStr + " 05:20:00", '%Y-%m-%d %X')
    print(f"开始时间是： {startDate}")
    print(f"结束时间是： {endDate}")
    getSettleChannelPaymetSettleSnListSql = f"select sid, pay_channel_id, settle_sn from settle_channel_payment  where create_time >= '{startDate}' and create_time <= '{endDate}' "
    # print(f"【sql】 获取医药订单lsit的sql 是：{getSettleChannelPaymetSettleSnListSql}")
    cursor.execute(getSettleChannelPaymetSettleSnListSql)
    return cursor.fetchall()


class ItemDeliveryInfo:
    def __init__(self, orderSn, sellerId, shippingFee, itemId, orderPrice, deliveryPrice, finalCount):
        self.orderSn = orderSn
        self.sellerId = sellerId
        self.shippingFee = shippingFee
        self.itemId = itemId
        self.orderPrice = orderPrice
        self.deliveryPrice = deliveryPrice
        self.finalCount = finalCount


class OrderDeliveryInfo:
    def __init__(self, orderSn):
        self.orderSn = orderSn
        self.orderDeliveryAmount = 0.0


if __name__ == "__main__":
    update_settle_channel_payment_by_sql()
